package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.oocl.dao.FoodDao;
import com.oocl.pojo.Food;
import com.oocl.util.DBUtil;

public class FoodDaoImpl implements FoodDao{

	public List<Food> loadFoodsByicnum(String icnum) throws SQLException{
		String sql="select * from foods where icnum=?";
		List<Food> flist=new ArrayList<Food>();
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, icnum);
		rs=pst.executeQuery();
		while(rs.next()){
			Food f=new Food(
					rs.getString("fid"), 
					icnum, 
					rs.getString("fname"),
					rs.getDouble("fprice"),
					rs.getString("ftype"), 
					rs.getString("fimage"));
			flist.add(f);
		}
		DBUtil.close(conn, pst, rs);
		return flist;
	}

	public List<Food> loadFoodsByType(String icnum, String type)
			throws SQLException {
		String sql="select * from foods where icnum=? and ftype=?";
		List<Food> flist=new ArrayList<Food>();
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, icnum);
		pst.setString(2, type);
		rs=pst.executeQuery();
		while(rs.next()){
			Food f=new Food(
					rs.getString("fid"), 
					icnum, 
					rs.getString("fname"), 
					rs.getDouble("fprice"), 
					type, 
					rs.getString("fimage"));
			flist.add(f);
		}
		DBUtil.close(conn, pst, rs);
		return flist;
	}

	public String addFood(Food f) throws SQLException {
		
		String sql="insert into foods (fid,icnum,fname,fprice,ftype,fimage) values (?,?,?,?,?,?)";
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		pst=conn.prepareStatement(sql);
		String fid=UUID.randomUUID().toString();
		int m=0;
		pst.setString(1,fid);
		pst.setString(2, f.getIcnum());
		pst.setString(3,f.getFname());
		pst.setDouble(4, f.getPrice());
		pst.setString(5, f.getFtype());
		pst.setString(6, f.getFimage());
		m=pst.executeUpdate();
		System.out.println(m);
		DBUtil.close(conn, pst, null);
		return fid;
	}
	
	public int delete(String fid) throws SQLException {
		String sql="delete foods where fid=?";
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, fid);
		int m=pst.executeUpdate();
		DBUtil.close(conn, pst, null);
		return m;
	}

	public int update(Food f) throws SQLException {
		String sql="update foods set fname=?,fprice=?,ftype=?,fimage=? where fid=? and icnum=?";
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, f.getFname());
		pst.setDouble(2, f.getPrice());
		pst.setString(3, f.getFtype());
		pst.setString(4, f.getFimage());
		pst.setString(5, f.getFid());
		pst.setString(6, f.getIcnum());
		int m=pst.executeUpdate();
		DBUtil.close(conn, pst, null);
		return m;
	}

	public Food loadOne(String fid) throws SQLException {
		String sql="select * from foods where fid=?";
		Food f=new Food();
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, fid);
		rs=pst.executeQuery();
		while(rs.next()){
			f.setFid(fid);
			f.setIcnum(rs.getString("icnum"));
			f.setFname(rs.getString("fname"));
			f.setPrice(rs.getDouble("fprice"));
			f.setFtype(rs.getString("ftype"));
			f.setFimage(rs.getString("fimage"));
		}
		DBUtil.close(conn, pst, rs);
		return f;
		
	}


	public List<Food> loadAll() throws SQLException {
		// TODO Auto-generated method stub
		String sql="select * from foods";
		List<Food> flist=new ArrayList<Food>();
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()){
			Food f=new Food(
					rs.getString("fid"), 
					rs.getString("icnum"), 
					rs.getString("fname"),
					rs.getDouble("fprice"),
					rs.getString("ftype"), 
					rs.getString("fimage"));
			flist.add(f);
		}
		DBUtil.close(conn, pst, rs);
		return flist;
	}

	public List<Food> loadFoodsByIcnum(String icnum) throws SQLException {
		String sql="select * from foods where icnum=?";
		List<Food> flist=new ArrayList<Food>();
		Connection conn=DBUtil.createConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, icnum);
		rs=pst.executeQuery();
		while(rs.next()){
			Food f=new Food(
					rs.getString("fid"), 
					icnum, 
					rs.getString("fname"),
					rs.getDouble("fprice"),
					rs.getString("ftype"), 
					rs.getString("fimage"));
			flist.add(f);
		}
		DBUtil.close(conn, pst, rs);
		return flist;
	}


}
